# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import pymysql
import requests
from scrapy.pipelines.images import ImagesPipeline
import scrapy
class PicsPlieLine(ImagesPipeline):
    def get_media_requests(self, item, info):
        img_url = item['url']
        yield scrapy.Request(img_url)

    def file_path(self,request,response=None,info=None):
        img_name = request.url.split('/')[-1]
        print(img_name)
        return img_name
    def item_completed(self, results, item, info):
        return item  #返回给下一个将被执行的管道类

class CrawlspiderPipeline(ImagesPipeline):
    conn = None
    cour = None
    def open_spider(self,spider):
        self.conn=pymysql.connect(host='localhost',port=3306,user='root',password='123456',db='xiaowei')
    def create_table(self):
        self.cour=self.conn.cursor()
        sql = """
        create TABLE if not exists lianjiaershou(
        id int not NULL auto_increment primary KEY,
        title VARCHAR(120),
        price VARCHAR(120),
        jy_shuxi VARCHAR(120),
        xq_name VARCHAR(120),
        xq_address VARCHAR(120),
        kf_time VARCHAR(120),
        number VARCHAR(120),
        hxfj text(0)
        ) ;
        """
        self.cour.execute(sql)
    def process_item(self, item, spider):
        self.cour = self.conn.cursor()
        title = item['title']
        price = item['price']
        jy_shuxi = item['jy_shuxi']
        xq_name = item['xq_name']
        xq_address = item['xq_address']
        kf_time = item['kf_time']
        number = item['number']
        hxfj = item['hxfj']
        sql = 'insert into lianjiaershou values("%s","%s","%s","%s","%s","%s","%s","%s")'%(title,price,jy_shuxi,xq_name,xq_address,kf_time,number,hxfj)

        try:
            self.cour.execute(sql)
            self.conn.commit()
        except Exception as ex:
            print(ex)
            self.conn.rollback()

        return item
    def close_spider(self,spider):
        self.conn.close()
        self.cour.close()
